---
sidebar_label: Custom SQL functions
sidebar_position: 2
description: Set default field values in Postgres using SQL functions in Hasura
keywords:
  - hasura
  - docs
  - postgres
  - schema
  - default value
  - sql function
  - stored procedure
---

import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';
import Thumbnail from '@site/src/components/Thumbnail';
import GraphiQLIDE from '@site/src/components/GraphiQLIDE';

# Postgres: Setting Values of Fields Using SQL Functions

## Introduction

Let's say you want to set the value of some fields as the output of some
[custom SQL functions](https://www.postgresql.org/docs/current/sql-createfunction.html) (a.k.a. stored procedures). This
is useful to set values of fields which depend on other fields passed in the input. E.g. set `submission_time` of an
online quiz as 1 hour from the `start_time`.

This can be achieved by:

1.  Modifying the table to allow the columns we want to be set by the SQL functions to be nullable (to allow the initial
    insert before the SQL function is run).
2.  Creating an insert/update trigger on the table that calls your SQL function and sets the output values in the output
    columns.
3.  Making your mutation requests without setting the SQL function output columns.

:::info Note

This approach enforces the value set in the field to always be the result of the defined SQL function even if a value is
explicitly passed in the insert object.

:::

**For example**, say we have a table `sql_function_table` with columns `input` and `output` and we would like to set the
value of the `output` column as the uppercased value of the string received in the `input` field.

## Step 1: Modify the table

Modify the table `sql_function_table` and make its `output` column nullable.

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

Open the Console and head to `Data -> [sql_function_table] -> Modify`:

<Thumbnail src="/img/schema/modify-sql-fn-table.png" alt="Modify the table" />

</TabItem>
<TabItem value="cli" label="CLI">

[Create a migration manually](/migrations-metadata-seeds/manage-migrations.mdx#create-manual-migrations) and add the
following SQL statement to the `up.sql` file:

```sql
ALTER TABLE "public"."sql_function_table" ALTER COLUMN "output" DROP NOT NULL;
```

Add the following statement to the `down.sql` file in case you need to
[roll back](/migrations-metadata-seeds/manage-migrations.mdx#roll-back-migrations) the above statement:

```sql
ALTER TABLE "public"."sql_function_table" ALTER COLUMN "output" SET NOT NULL;
```

Apply the migration by running:

```bash
hasura migrate apply
```

</TabItem>
<TabItem value="api" label="API">

You can modify a table column by using the [run_sql](/api-reference/schema-api/run-sql.mdx#schema-run-sql) schema API:

```http
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "run_sql",
  "args": {
    "source": "<db_name>",
    "sql": "ALTER TABLE sql_function_table ALTER COLUMN output DROP NOT NULL;"
  }
}
```

</TabItem>
</Tabs>

## Step 2: Create a trigger

The below SQL defines a `trigger` which will simply uppercase the value passed in the `input` field and set it to the
`output` field whenever an insert or update is made to the `sql_function_table`:

```plsql
CREATE FUNCTION test_func() RETURNS trigger AS $emp_stamp$
      BEGIN
          NEW.output := UPPER(NEW.input);
          RETURN NEW;
      END;
  $emp_stamp$ LANGUAGE plpgsql;

  CREATE TRIGGER test_trigger BEFORE INSERT OR UPDATE ON sql_function_table
      FOR EACH ROW EXECUTE PROCEDURE test_func();
```

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

Head to `Data -> SQL` and run the above SQL:

<Thumbnail src="/img/schema/create-trigger.png" alt="Create a trigger with SQL" />

</TabItem>
<TabItem value="cli" label="CLI">

[Create a migration manually](/migrations-metadata-seeds/manage-migrations.mdx#create-manual-migrations) and add the
above SQL to the `up.sql` file. Also, add a statement to revert the previous statement to the `down.sql`.

Apply the migration by running:

```bash
hasura migrate apply
```

</TabItem>
<TabItem value="api" label="API">

You can create a trigger by using the [run_sql](/api-reference/schema-api/run-sql.mdx#schema-run-sql) schema API:

```http
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "run_sql",
  "args": {
    "source": "<db_name>",
    "sql": "<above SQL>"
  }
}
```

</TabItem>
</Tabs>

## Step 3: Run an insert mutation

Run a mutation to insert an object with (input = "yabba dabba doo!", output=null) and you'll see the output value
(output="YABBA DABBA DOO!") will be set automatically.

<GraphiQLIDE
  query={`mutation {
  insert_sql_function_table (
    objects: [
      {input: "yabba dabba doo!"}
    ]
  ) {
    returning {
      input
      output
    }
  }
}`}
  response={`{
  "data": {
    "insert_sql_function_table": {
      "returning": [
        {
          "input": "yabba dabba doo!",
          "output": "YABBA DABBA DOO!"
        }
      ]
    }
  }
}`}
/>

## Also see

- [Postgres: Setting default values for fields using Postgres defaults](/schema/postgres/default-values/postgres-defaults.mdx)
- [Postgres: Setting values for fields using role-based column presets](/schema/postgres/default-values/column-presets.mdx)
